package com.cy.poi;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.Locale;

public class PoiLearn {


	private static final String TEMPLATE0 = "INSERT INTO auto_tag_config (tag_name, tag_desc, TABLE_NAME, table_desc, partition_type, field_name, field_desc, field_type, id_type, action_id, biz_type, valid_date,  create_time, template_name)\n" +
			"VALUES \n";

	private static final String TEMPLATE1 =
			"('#{tag_name}',\n" +
			"        '#{tag_desc}',\n" +
			"        '#{TABLE_NAME}',\n" +
			"        '#{table_desc}',\n" +
			"        'DAY',\n" +
			"        '#{field_name}',\n" +
			"        '#{field_desc}',\n" +
			"        '#{field_type}',\n" +
			"        'UID',\n" +
			"        #{action_id},\n" +
			"        '#{biz_type}',\n" +
			"        '2022-12-26',\n" +
			"        now(),\n" +
			"        '#{template_name}'),";

	public static void main(String[] args) throws IOException {
		FileInputStream excelFile = new FileInputStream(new File("D:\\1.xlsx"));
		Workbook workbook = new XSSFWorkbook(excelFile);
		LinkedList<String> sqls = new LinkedList<>();
		sqls.add(TEMPLATE0);
		int maxLength = 0;
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
			Sheet sheet = workbook.getSheetAt(i);
			for (Row currentRow : sheet) {
				if (currentRow.getRowNum() == 0) {
					continue;
				}
				DataFormatter dataFormatter = new DataFormatter(Locale.CHINA);
				String tagName = dataFormatter.formatCellValue(currentRow.getCell(0)).trim();
				String fieldName = dataFormatter.formatCellValue(currentRow.getCell(1)).trim();
				String fieldType = dataFormatter.formatCellValue(currentRow.getCell(2)).trim();
				String tagDesc = dataFormatter.formatCellValue(currentRow.getCell(3)).trim();
				if (tagDesc.length() > maxLength) {
					maxLength = tagDesc.length();
				}

				String tableName = dataFormatter.formatCellValue(currentRow.getCell(4)).trim();
				String tableDesc = dataFormatter.formatCellValue(currentRow.getCell(5)).trim();
				String actionId = dataFormatter.formatCellValue(currentRow.getCell(6)).trim();
				String templateName = dataFormatter.formatCellValue(currentRow.getCell(7)).trim();
				String bizType = dataFormatter.formatCellValue(currentRow.getCell(8)).trim();


				String[] param = {"#{tag_name}", "#{tag_desc}", "#{TABLE_NAME}", "#{table_desc}", "#{field_name}", "#{field_desc}", "#{field_type}", "#{action_id}", "#{biz_type}", "#{template_name}"};
				String[] replace = {tagName, tagDesc, tableName, tableDesc, fieldName, "", fieldType, actionId, bizType, templateName};
				String s = StringUtils.replaceEach(TEMPLATE1, param, replace);
				sqls.add(s);
			}



		}

		String lastSql = sqls.removeLast();
		String rightLastSql = lastSql.substring(0, lastSql.length() - 1);
		sqls.add(rightLastSql);

		FileUtils.writeLines(new File("D:/1.sql"),sqls);
		System.out.println(maxLength);
	}
}
